﻿using System;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using SqlSystemWatcher.Common;

namespace SqlSystemWatcher.ExecSessionLog
{
    public class ExecSessionLoggerCore
    {
        private const string SourceClassName = "ExecSessionLoggerCore";
        private const string ExecSessionLogInserSuccesfull = "ExecSessionLogInsert is successfully completed.";

        private const string LogCheckIntervalLastCheckUpdate = "LogCheckIntervalLastCheckUpdate is successfully completed.";

        private readonly DataSet _ds;
        private readonly string _instanceName;

        public bool IsFinished = false;

        public ExecSessionLoggerCore(string sqlServerInstanceName)
        {
            _ds = new DataSet();
            _instanceName = sqlServerInstanceName;
        }

        public void DoIt()
        {
            using (var backgroundWorker = new BackgroundWorker())
            {
                backgroundWorker.DoWork += BackgroundWorkerOnDoWork;
                backgroundWorker.RunWorkerAsync();
            }
        }

        private void BackgroundWorkerOnDoWork(object sender, DoWorkEventArgs e)
        {
            string conStringSessionLog = @"Data Source=" + _instanceName +
                                         ";Initial Catalog=master; Integrated Security=True";

            const string sqlCommandText = @"SELECT  dm_es.session_id					        SessionId,
                                            dm_es.status								        SessionStatus,
                                            dm_es.login_name							        LoginName,
                                            dm_es.[HOST_NAME]							        HostName,
                                            dm_er.blocking_session_id					        BlockingSessionId,
                                            DB_NAME(dm_er.database_id)				            DatabaseName,
                                            dm_er.command								        CommandType,
                                            dm_es.cpu_time							            CpuTime,
                                            dm_es.reads								            Reads,
                                            dm_es.writes								        Writes,
                                            dm_ec.last_write							        LastWrite,
                                            dm_es.[program_name]						        ProgramName,
                                            dm_er.wait_type							            WaitType,
                                            dm_er.wait_time							            WaitTime,
                                            dm_er.last_wait_type						        LastWaitType,
                                            dm_er.wait_resource						            WaitResource,
                                            CASE dm_es.transaction_isolation_level
                                                WHEN 0 THEN 'Unspecified'
                                                WHEN 1 THEN 'ReadUncommitted'
                                                WHEN 2 THEN 'ReadCommitted'
                                                WHEN 3 THEN 'Repeatable'
                                                WHEN 4 THEN 'Serializable'
                                                WHEN 5 THEN 'Snapshot'
                                            END AS									            TransactionIsolationLevel,
                                            OBJECT_NAME(dm_esh.objectid, dm_er.database_id)       ObjectName ,
                                            SUBSTRING(dm_esh.text, dm_er.statement_start_offset / 2,
                                            ( CASE WHEN dm_er.statement_end_offset = -1
                                            THEN DATALENGTH(dm_esh.text)
                                            ELSE dm_er.statement_end_offset
                                            END - dm_er.statement_start_offset ) / 2)           ExecutingStatement ,
                                            dm_eph.query_plan							            QueryPlan,
                                            GETDATE()                                           LastModifiedDate
                                            FROM    sys.dm_exec_sessions      dm_es
                                            LEFT JOIN sys.dm_exec_requests    dm_er ON dm_es.session_id = dm_er.session_id
                                            LEFT JOIN sys.dm_exec_connections dm_ec ON dm_es.session_id = dm_ec.session_id
                                            
                                            CROSS APPLY sys.dm_exec_sql_text(dm_er.sql_handle)    dm_esh
                                            CROSS APPLY sys.dm_exec_query_plan(dm_er.plan_handle) dm_eph

                                            --WHERE DB_NAME(dm_er.database_id) <> 'master' AND dm_es.cpu_time <> 0
                                            ORDER BY dm_es.session_id";

            if (_ds.Tables.Contains("RawSessionLog"))
            {
                _ds.Tables["RawSessionLog"].Clear();
            }
            else
            {
                _ds.Tables.Add("RawSessionLog");
            }

            var sqlDaRawSessionLog = new SqlDataAdapter {SelectCommand = new SqlCommand(sqlCommandText)};
            sqlDaRawSessionLog.SelectCommand.Connection = new SqlConnection(conStringSessionLog);

            string programCode = @"sqlDaRawSessionLog.Fill(_ds.Tables[""RawSessionLog""]);";
            string connectionString = sqlDaRawSessionLog.SelectCommand.Connection.ConnectionString;
            string commandText = sqlDaRawSessionLog.SelectCommand.CommandText;

            try
            {
                sqlDaRawSessionLog.Fill(_ds.Tables["RawSessionLog"]);
            }
            catch (Exception exception)
            {
                CustomException.WriteInfoScreen(CustomException.InfoScreenLevel.Exception, SourceClassName, programCode, connectionString, sqlDaRawSessionLog.SelectCommand.Connection.DataSource, commandText, exception, "");

                IsFinished = true;

                return;
            }

            CustomException.WriteInfoScreen(CustomException.InfoScreenLevel.SuccessfullyCompleted, SourceClassName, programCode, connectionString, sqlDaRawSessionLog.SelectCommand.Connection.DataSource, commandText, null, "");

            Guid sqlServerSessionLogSetId = Guid.NewGuid();

            foreach (DataRow rawLogRow in _ds.Tables["RawSessionLog"].Rows)
            {
                string connStr = ConfigurationManager.ConnectionStrings["ConStr"].ToString();
                var sqlConnection = new SqlConnection(connStr);

                var sqlDaSessionLog = new SqlDataAdapter
                    {
                        InsertCommand = new SqlCommand("[dbo].[ExecSessionLogInsert]")
                    };

                sqlDaSessionLog.InsertCommand.Connection = sqlConnection;

                programCode = @"sqlDaSessionLog.InsertCommand.Connection.Open();";
                connectionString = sqlDaSessionLog.InsertCommand.Connection.ConnectionString;
                commandText = "";
                try
                {
                    sqlDaSessionLog.InsertCommand.Connection.Open();
                }
                catch (Exception exception)
                {
                    CustomException.WriteInfoScreen(CustomException.InfoScreenLevel.Exception, SourceClassName, programCode, connectionString, sqlDaSessionLog.InsertCommand.Connection.DataSource, commandText, exception, "");

                    IsFinished = true;

                    return;
                }

                sqlDaSessionLog.InsertCommand.CommandType = CommandType.StoredProcedure;
                sqlDaSessionLog.InsertCommand.Parameters.AddWithValue("@BlockingSessionId",
                                                                      rawLogRow["BlockingSessionId"]);
                sqlDaSessionLog.InsertCommand.Parameters.AddWithValue("@CommandType", rawLogRow["CommandType"]);
                sqlDaSessionLog.InsertCommand.Parameters.AddWithValue("@CpuTime", rawLogRow["CpuTime"]);
                sqlDaSessionLog.InsertCommand.Parameters.AddWithValue("@DatabaseName", rawLogRow["DatabaseName"]);
                sqlDaSessionLog.InsertCommand.Parameters.AddWithValue("@ExecutingStatement",
                                                                      rawLogRow["ExecutingStatement"]);
                sqlDaSessionLog.InsertCommand.Parameters.AddWithValue("@HostName", rawLogRow["HostName"]);
                sqlDaSessionLog.InsertCommand.Parameters.AddWithValue("@LastModifiedDate",
                                                                      rawLogRow["LastModifiedDate"]);
                sqlDaSessionLog.InsertCommand.Parameters.AddWithValue("@LastWaitType", rawLogRow["LastWaitType"]);
                sqlDaSessionLog.InsertCommand.Parameters.AddWithValue("@LastWrite", rawLogRow["LastWrite"]);
                sqlDaSessionLog.InsertCommand.Parameters.AddWithValue("@LoginName", rawLogRow["LoginName"]);
                sqlDaSessionLog.InsertCommand.Parameters.AddWithValue("@ObjectName", rawLogRow["ObjectName"]);
                sqlDaSessionLog.InsertCommand.Parameters.AddWithValue("@ProgramName", rawLogRow["ProgramName"]);
                if (!rawLogRow["QueryPlan"].Equals(null))
                    sqlDaSessionLog.InsertCommand.Parameters.AddWithValue("@QueryPlan", rawLogRow["QueryPlan"]);
                sqlDaSessionLog.InsertCommand.Parameters.AddWithValue("@Reads", rawLogRow["Reads"]);
                sqlDaSessionLog.InsertCommand.Parameters.AddWithValue("@InstanceName", _instanceName);
                sqlDaSessionLog.InsertCommand.Parameters.AddWithValue("@SessionId", rawLogRow["SessionId"]);
                sqlDaSessionLog.InsertCommand.Parameters.AddWithValue("@SessionStatus", rawLogRow["SessionStatus"]);
                sqlDaSessionLog.InsertCommand.Parameters.AddWithValue("@SessionLogSetId", sqlServerSessionLogSetId);
                sqlDaSessionLog.InsertCommand.Parameters.AddWithValue("@TransactionIsolationLevel",
                                                                      rawLogRow["TransactionIsolationLevel"]);
                sqlDaSessionLog.InsertCommand.Parameters.AddWithValue("@WaitResource", rawLogRow["WaitResource"]);
                sqlDaSessionLog.InsertCommand.Parameters.AddWithValue("@WaitTime", rawLogRow["WaitTime"]);
                sqlDaSessionLog.InsertCommand.Parameters.AddWithValue("@WaitType", rawLogRow["WaitType"]);
                sqlDaSessionLog.InsertCommand.Parameters.AddWithValue("@Writes", rawLogRow["Writes"]);

                programCode = @"sqlDaSessionLog.InsertCommand.ExecuteNonQuery();";
                connectionString = sqlDaSessionLog.InsertCommand.Connection.ConnectionString;
                commandText = sqlDaSessionLog.InsertCommand.CommandText;

                try
                {
                    sqlDaSessionLog.InsertCommand.ExecuteNonQuery();
                }
                catch (Exception exception)
                {
                    CustomException.WriteInfoScreen(CustomException.InfoScreenLevel.Exception, SourceClassName, programCode, connectionString, sqlDaSessionLog.InsertCommand.Connection.DataSource, commandText, exception, "");

                    IsFinished = true;

                    return;
                }
                finally
                {
                    sqlDaSessionLog.InsertCommand.Connection.Close();
                }
            }

            CustomException.WriteInfoScreen(CustomException.InfoScreenLevel.SuccessfullyCompleted, SourceClassName, programCode, connectionString, sqlDaRawSessionLog.SelectCommand.Connection.DataSource, commandText, null, ExecSessionLogInserSuccesfull);

            UpdateInstanceLastCheck(_instanceName);

            IsFinished = true;
        }

        private void UpdateInstanceLastCheck(string instanceName)
        {
            string conStr = ConfigurationManager.ConnectionStrings["ConStr"].ToString();
            var sqlConnection = new SqlConnection(conStr);

            var sqlDaSqlInstance = new SqlDataAdapter
                {
                    UpdateCommand =
                        new SqlCommand("[dbo].[LogCheckIntervalLastCheckUpdate]")
                };
            sqlDaSqlInstance.UpdateCommand.CommandType = CommandType.StoredProcedure;
            sqlDaSqlInstance.UpdateCommand.Connection = sqlConnection;

            sqlDaSqlInstance.UpdateCommand.Parameters.AddWithValue("@InstanceName", instanceName);
            sqlDaSqlInstance.UpdateCommand.Parameters.AddWithValue("@LogType", "SessionLog");

            string programCode = @"sqlDaSqlInstance.UpdateCommand.Connection.Open();";
            string connectionString = sqlDaSqlInstance.UpdateCommand.Connection.ConnectionString;
            string commandText = sqlDaSqlInstance.UpdateCommand.CommandText;
            try
            {
                sqlDaSqlInstance.UpdateCommand.Connection.Open();
            }
            catch (Exception exception)
            {
                CustomException.WriteInfoScreen(CustomException.InfoScreenLevel.Exception, SourceClassName, programCode, connectionString, sqlDaSqlInstance.UpdateCommand.Connection.DataSource, commandText, exception, "");
                return;
            }

            programCode = @"sqlDaSqlInstance.UpdateCommand.ExecuteNonQuery();";
            connectionString = sqlDaSqlInstance.UpdateCommand.Connection.ConnectionString;
            commandText = sqlDaSqlInstance.UpdateCommand.CommandText;
            try
            {
                sqlDaSqlInstance.UpdateCommand.ExecuteNonQuery();
                sqlDaSqlInstance.UpdateCommand.Connection.Close();
            }
            catch (Exception exception)
            {
                CustomException.WriteInfoScreen(CustomException.InfoScreenLevel.Exception, SourceClassName, programCode, connectionString, sqlDaSqlInstance.UpdateCommand.Connection.DataSource, commandText, exception, "");
                sqlDaSqlInstance.UpdateCommand.Connection.Close();
                return;
            }

            CustomException.WriteInfoScreen(CustomException.InfoScreenLevel.SuccessfullyCompleted, SourceClassName, programCode, connectionString, sqlDaSqlInstance.UpdateCommand.Connection.DataSource, commandText, null, LogCheckIntervalLastCheckUpdate);
        }
    }
}